﻿using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.IO;
using System.Text;
using System.Web;
using GMAC.ReportingSystem.Business.Entities;
using GMAC.ReportingSystem.Business.Components;
using System.Data.Common;
using System.Collections;
using System.Configuration;
//using Ionic.Zip;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace GMAC.DirectDebit.Common
{
    public class FileFunctions
    {
        # region Properties

        public string fileName { get; set; }

        # endregion

        # region Constructor

        public FileFunctions()
        {
            fileName = DateTime.Today.ToShortDateString();
        }

        public FileFunctions(string FileName)
        {
            fileName = FileName;
        }

        # endregion

        //# region Public Methods

        //public StringBuilder BuildFile(ChargeEntitiesCollection chargeCollection, ChargeEntitiesCollection chargeCollectionErrors, ConfigurationFile fileConfiguration, Product infoProduct, int type, string split, ProductCollection productList)
        //{
        //    CalculteDocumentTotalAmount(ref chargeCollection, fileConfiguration,infoProduct);
        //    CalculteDocumentTotalAmount(ref chargeCollectionErrors, fileConfiguration, infoProduct);
        //    //Modificar Correlativo Exitosos
        //    for (int i = 0; i < chargeCollection.Count; i++)
        //    {
        //        chargeCollection[i].Correlative =  i + 1;
        //    }
        //    //Modificar Correlativo de la Coleccion de ERRORES
        //    for (int i = 0; i < chargeCollectionErrors.Count; i++)
        //    {
        //        chargeCollectionErrors[i].Correlative = chargeCollection.Count + i + 1;
        //    }

        //    //Procesar Archivo
        //    int rowNumber = 1;
        //    StringBuilder str = new StringBuilder();



        //    if (chargeCollection.Count > 0) // SI HAY REGISTROS EXITOSO
        //    {
        //        // Cargo la estructura del encabezado
        //        if (fileConfiguration.StructureFile.Cabeceras.Count > 0)
        //        {
        //            foreach (ItemStructureFile item in fileConfiguration.StructureFile.Cabeceras)
        //            {
        //                if (rowNumber < item.RowNumber)
        //                    str.AppendLine();

        //                str.Append(LoadFileSection(item, chargeCollection[0], chargeCollection, infoProduct, type, split));
        //                rowNumber = (int)item.RowNumber;
        //            }
        //            str.AppendLine();
        //            rowNumber = 1;
        //        }
        //        if (fileConfiguration.StructureFile.Cuerpos.Count > 0)
        //        {
        //            //SI la configuracion es excel cargo los headers constituidos por los nombres de cada campo
        //            if (fileConfiguration.FileFormat == (int)GeneralEnumTypes.FileFormat.xls)
        //            {
        //                foreach (ItemStructureFile item in fileConfiguration.StructureFile.Cuerpos)
        //                {
        //                    if (rowNumber < item.RowNumber)
        //                        str.AppendLine();

        //                    str.Append(item.FieldName);
        //                    str.Append(split);
        //                    rowNumber = (int)item.RowNumber;
        //                }
        //                str.AppendLine();
        //                rowNumber = 1;
        //            }
        //            // Cargo la estructura del Cuerpo
        //            foreach (ChargeEntities charge in chargeCollection)
        //            {
        //                foreach (ItemStructureFile item in fileConfiguration.StructureFile.Cuerpos)
        //                {
        //                    if (rowNumber < item.RowNumber)
        //                        str.AppendLine();
        //                    if (productList.Count > 0)
        //                    {
        //                        var trueProduct = new Product();
        //                        trueProduct = productList.Find(PredicateParameters(charge.AccountTypeId.ToString()));
        //                        str.Append(LoadFileSection(item, charge, chargeCollection, trueProduct, type, split));
        //                    }
        //                    else
        //                    {
        //                        str.Append(LoadFileSection(item, charge, chargeCollection, infoProduct, type, split));
        //                    }
        //                    rowNumber = (int)item.RowNumber;
        //                }
        //                str.AppendLine();
        //                rowNumber = 1;
        //            }
        //        }
        //    }
        //    else 
        //    { 
        //        //CARGO LOS TITULOS SOLAMENTE DE LOS EXITOSOS
        //        if (fileConfiguration.StructureFile.Cuerpos.Count > 0)
        //        {
        //            //SI la configuracion es excel cargo los headers constituidos por los nombres de cada campo
        //            if (fileConfiguration.FileFormat == (int)GeneralEnumTypes.FileFormat.xls)
        //            {
        //                foreach (ItemStructureFile item in fileConfiguration.StructureFile.Cuerpos)
        //                {
        //                    if (rowNumber < item.RowNumber)
        //                        str.AppendLine();

        //                    str.Append(item.FieldName);
        //                    str.Append(split);
        //                    rowNumber = (int)item.RowNumber;
        //                }
        //                str.AppendLine();
        //                rowNumber = 1;
        //            }
        //        }
        //    }
            

        //    if (chargeCollectionErrors.Count>0) // SI HAY REGISTROS CON ERRORES
        //    {
        //        rowNumber = 1;
        //        if (fileConfiguration.StructureFile.CuerposFallidos.Count > 0) 
        //        //SI HAY CONFIGURACION PARA CUERPO FALLIDOS
        //        {
        //            // Cargo la estructura del Cuerpo Failed
        //            foreach (ChargeEntities charge in chargeCollectionErrors)
        //            {
        //                foreach (ItemStructureFile item in fileConfiguration.StructureFile.CuerposFallidos)
        //                {
        //                    if (rowNumber < item.RowNumber)
        //                        str.AppendLine();
        //                    if (productList.Count > 0)
        //                    {
        //                        var trueProduct = new Product();
        //                        trueProduct = productList.Find(PredicateParameters(charge.AccountTypeId.ToString()));
        //                        str.Append(LoadFileSection(item, charge, chargeCollectionErrors, trueProduct, type, split));
        //                    }
        //                    else
        //                    {
        //                        str.Append(LoadFileSection(item, charge, chargeCollectionErrors, infoProduct, type, split));
        //                    }
        //                    rowNumber = (int)item.RowNumber;
        //                }
        //                str.AppendLine();
        //                rowNumber = 1;
        //            }

        //        }
        //    }

        //    //CARGAR LOS FOOTER
        //    if (chargeCollection.Count > 0) // SI HAY REGISTROS EXITOSO
        //    {
        //        if (fileConfiguration.StructureFile.PiePaginas.Count > 0)
        //        {
        //            // Cargo la estructura del Pie de Página
        //            foreach (ItemStructureFile item in fileConfiguration.StructureFile.PiePaginas)
        //            {
        //                if (rowNumber < item.RowNumber)
        //                    str.AppendLine();

        //                str.Append(LoadFileSection(item, chargeCollection[0], chargeCollection, infoProduct, type, split));
        //                rowNumber = (int)item.RowNumber;
        //            }
        //            str.AppendLine();
        //            rowNumber = 1;
        //        }
        //    }

        //    if (chargeCollectionErrors.Count > 0) // SI HAY REGISTROS ERRONEOS
        //    {
        //        if (fileConfiguration.StructureFile.PiePaginasFallidos.Count > 0)
        //        //SI HAY CONFIGURACION PARA PIE FALLIDOS
        //        {
        //            // Cargo la estructura del Pie de Página Fallidos
        //            foreach (ItemStructureFile item in fileConfiguration.StructureFile.PiePaginasFallidos)
        //            {
        //                if (rowNumber < item.RowNumber)
        //                    str.AppendLine();

        //                str.Append(LoadFileSection(item, chargeCollectionErrors[0], chargeCollectionErrors, infoProduct, type, split));
        //                rowNumber = (int)item.RowNumber;
        //            }
        //        }
        //    }
        //    //quita la linea en blanco, es mas facil hacerlo aqui, que cambiar la logica aplicada arriba 
        //    //para hacer salto de linea
        //    if (str.Length>0)
        //    {
        //       int strLenght = str.Length -1;
        //       if (str[strLenght].ToString().ToLower() == "\n")
        //            str.Remove(strLenght -1, 2);  
        //    }
           
        //    return str;
        //}
        //public void CalculteDocumentTotalAmount(ref ChargeEntitiesCollection chargeCollection, ConfigurationFile fileConfiguration, Product infoProduct)
        //{
            
        //    string ValueDocumentTotalAmount = "";
        //    var  itemDocumentTotalAmount = (from i in fileConfiguration.StructureFile.Cabeceras where i.ValueType.Split('_')[1] == "DocumentTotalAmount" select i.Value);
        //     var results0 = itemDocumentTotalAmount.ToList();
        //    if (results0.Count > 0)
        //    {
        //        ValueDocumentTotalAmount = results0[0].ToString();
        //    }
        //    else 
        //    { 
        //        itemDocumentTotalAmount = (from i in fileConfiguration.StructureFile.Cuerpos where i.ValueType.Split('_')[1] == "DocumentTotalAmount" select i.Value);
        //        results0 = itemDocumentTotalAmount.ToList();
        //        if (results0.Count > 0)
        //        {
        //            ValueDocumentTotalAmount = results0[0].ToString();
        //        }
        //        else 
        //        { 
        //            itemDocumentTotalAmount = (from i in fileConfiguration.StructureFile.PiePaginas where i.ValueType.Split('_')[1] == "DocumentTotalAmount" select i.Value);
        //            results0 = itemDocumentTotalAmount.ToList();
        //            if (results0.Count > 0)
        //            {
        //                ValueDocumentTotalAmount = results0[0].ToString();
        //            }
        //        }
        //    }
        //    ItemStructureFile ValueTypeValueDocumentTotalAmount = null;
        //    if (ValueDocumentTotalAmount != "")
        //    {
        //        var  itemValueDocumentTotalAmount = (from i in fileConfiguration.StructureFile.Cabeceras where i.FieldName == ValueDocumentTotalAmount select i);
        //        var results = itemValueDocumentTotalAmount.ToList();
        //        if (results.Count>0)
        //        {
        //            ValueTypeValueDocumentTotalAmount = results[0];
        //        }
        //        else 
        //        { 
        //            itemValueDocumentTotalAmount = (from i in fileConfiguration.StructureFile.Cuerpos where i.FieldName == ValueDocumentTotalAmount select i);
        //            results = itemValueDocumentTotalAmount.ToList();
        //            if (results.Count > 0)
        //            {
        //                ValueTypeValueDocumentTotalAmount = results[0];
        //            }
        //            else 
        //            { 
        //                itemValueDocumentTotalAmount = (from i in fileConfiguration.StructureFile.PiePaginas where i.FieldName == ValueDocumentTotalAmount select i);
        //                results = itemValueDocumentTotalAmount.ToList();
        //                if (results.Count > 0)
        //                {
        //                    ValueTypeValueDocumentTotalAmount = results[0];
        //                }
        //            }
        //        }
        //    }
        //    decimal totalmonto=0;
        //    Type t = typeof(ChargeEntities);
        //    if (ValueTypeValueDocumentTotalAmount!=null)
        //    {
        //      string type =  ValueTypeValueDocumentTotalAmount.ValueType.Split('_')[1];
        //      if (type == "Calculate")
        //      {
        //          char[] delimit = new char[] { '+', '-' };
        //          int x = 0;
        //          CalculateFieldsCollection cfc = new CalculateFieldsCollection();

        //          foreach (string subItem in ValueTypeValueDocumentTotalAmount.Value.Split(delimit))
        //          {
        //              x += subItem.Length;
        //              if (x < ValueTypeValueDocumentTotalAmount.Value.Length)
        //              {
        //                  CalculateFields cf = new CalculateFields()
        //                  {
        //                      Field = subItem,
        //                      OperationType = ValueTypeValueDocumentTotalAmount.Value.Trim().Substring(x, 1)
        //                  };
        //                  cfc.Add(cf);
        //              }
        //              x++;
        //          }
        //          ///////////
        //            decimal total = 0;
        //            for (int i = 0; i < cfc.Count; i++)
        //            {
        //                System.Reflection.PropertyInfo pi = t.GetProperty(cfc[i].Field);
        //                //decimal firtsValue = (decimal)t.GetProperty(cfc[i].Field).GetValue(chargeCollection, null);
        //                //decimal secondValue = (decimal)t.GetProperty(cfc[i + 1].Field).GetValue(chargeCollection, null);
        //                totalmonto = (from item in chargeCollection select (decimal)(pi.GetValue(item, null))).Sum();
        //                if (cfc[i].OperationType == "+")
        //                    total = total + totalmonto;
        //                else
        //                    total = total - totalmonto;
        //            }
        //          ////////

        //          totalmonto = total;
        //      }
        //      else 
        //      {
        //          System.Reflection.PropertyInfo pi = t.GetProperty(type);
                  
        //          totalmonto = (from i in chargeCollection select (decimal)(pi.GetValue(i,null))).Sum();
        //      }
        //    }
        //    chargeCollection.DocumentTotalAmount = totalmonto;
        //    chargeCollection.TotalRecords = chargeCollection.Count;
        //}
        //public void DownloadFile(int sendFileId, HttpResponse Response)
        //{
        //    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ToString());
        //    con.Open();

        //    SqlCommand comand = new SqlCommand(ConfigurationManager.AppSettings[0].ToString(), con);
        //    SqlTransaction tran = con.BeginTransaction(IsolationLevel.ReadCommitted);
        //    comand.Transaction = tran;

        //    comand.CommandType = CommandType.StoredProcedure;
        //    comand.Parameters.Add(new SqlParameter("SendFileId", sendFileId));


        //    using (SqlDataReader reader = comand.ExecuteReader())
        //    {
        //        while (reader.Read())
        //        {

        //            string path = reader.GetString(0);
        //            string fileName = path.Substring(path.Length - 36) + ".txt";
        //            byte[] buffer = new byte[(int)reader.GetValue(2)];
        //            SqlFileStream sqlFS = new SqlFileStream(path, (byte[])reader.GetValue(1), FileAccess.Read, FileOptions.SequentialScan, 0);
        //            int count = sqlFS.Read(buffer, 0, buffer.Length);
        //            if (fileName != null)
        //            {
        //                GenerateFileBinary(buffer, Response, fileName);
        //            }

        //        }
        //    }
        //    con.Close();
        //}
        //public void DownloadResponseFile(int responseFileId, HttpResponse Response)
        //{
        //    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ToString());
        //    con.Open();

        //    SqlCommand comand = new SqlCommand(ConfigurationManager.AppSettings["ResponseFileProcedure"].ToString(), con);
        //    SqlTransaction tran = con.BeginTransaction(IsolationLevel.ReadCommitted);
        //    comand.Transaction = tran;

        //    comand.CommandType = CommandType.StoredProcedure;
        //    comand.Parameters.Add(new SqlParameter("ResponseFileId", responseFileId));


        //    using (SqlDataReader reader = comand.ExecuteReader())
        //    {
        //        while (reader.Read())
        //        {

        //            string path = reader.GetString(0);
        //            string fileName = path.Substring(path.Length - 36) + ".txt";
        //            byte[] buffer = new byte[(int)reader.GetValue(2)];
        //            SqlFileStream sqlFS = new SqlFileStream(path, (byte[])reader.GetValue(1), FileAccess.Read, FileOptions.SequentialScan, 0);
        //            int count = sqlFS.Read(buffer, 0, buffer.Length);
        //            if (fileName != null)
        //            {
        //                GenerateFileBinary(buffer, Response, fileName);
        //            }

        //        }
        //    }
        //    con.Close();
        //}
        //public void GenerateFile(StringBuilder strBuilder, HttpResponse Response)
        //{
        //    Response.Clear();
        //    Response.AddHeader("content-disposition",
        //                       String.Format("attachment; filename={0}", fileName));
        //    Response.Charset = "";
        //    Response.ContentType = "text/plain";//"application/vnd.text";
        //    Response.Write(strBuilder.ToString());
        //    Response.End();
        //}
        //public void GenerateFileBinary(byte[]buffer, HttpResponse Response, string fileName) 
        //{
        //    Response.Clear();
        //    Response.AddHeader("content-disposition",
        //                       String.Format("attachment; filename={0}", fileName));
        //    Response.Charset = "";
        //    Response.ContentType = "text/plain";
        //    Response.BinaryWrite(buffer);
        //    Response.End();
        //}
        //public void GenerateZip(string sourcefile, HttpResponse Response, string fileName)
        //{
        //    byte[] buffer;
        //    using (FileStream fs = new FileStream(sourcefile, FileMode.Open, FileAccess.Read))
        //    {
        //        buffer = new byte[fs.Length];
        //        fs.Read(buffer, 0, Convert.ToInt32(fs.Length));
        //    }

        //    Response.Clear();
        //    Response.AddHeader("content-disposition",
        //                       String.Format("attachment; filename={0}", fileName));
        //    Response.Charset = "";
        //    Response.ContentType = System.Net.Mime.MediaTypeNames.Application.Zip;
        //    Response.BinaryWrite(buffer);
        //    Response.End();
        //}
        //public void SaveFile(string path, string name, string text)
        //{
        //    FileInfo fileinfo = new FileInfo(path + name);
        //    DirectoryInfo DirectoryInfo = new DirectoryInfo(path);

        //    if (!DirectoryInfo.Exists)
        //    {
        //         DirectoryInfo.Create();
        //    }   
        //        string fileName = path + name;
        //        FileStream stream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);
        //        StreamWriter writer = new StreamWriter(stream);

        //        writer.Write(text);
        //        writer.Close();

           
        //}
        //public string CreateZip(string path, ArrayList files, string filezipname)
        //{
        //    DirectoryInfo DirectoryInfo = new DirectoryInfo(path);
        //    if (!DirectoryInfo.Exists)
        //    {
        //        DirectoryInfo.Create();
        //    } 
        //    ZipFile zip = new ZipFile();
        //    string destinationfile = "";
        //    if (files.Count > 0)
        //    {
        //        destinationfile=path + filezipname + ".zip";
        //        foreach (string item in files)
        //        {
        //            zip.AddFile(item, "");
        //        }
        //        zip.Save(destinationfile);
        //    }
        //    return destinationfile;
        //}  

        //# endregion

        //# region Private Methods

        //private decimal CalculateFields(ItemStructureFile item, ChargeEntities charge)
        //{
        //    char[] delimit = new char[] { '+', '-' };
        //    int x = 0;
        //    CalculateFieldsCollection cfc = new CalculateFieldsCollection();

        //    foreach (string subItem in item.Value.Split(delimit))
        //    {
        //        x += subItem.Length;
        //        if (x < item.Value.Length)
        //        {
        //            CalculateFields cf = new CalculateFields()
        //            {
        //                Field = subItem,
        //                OperationType = item.Value.Trim().Substring(x, 1)
        //            };
        //            cfc.Add(cf);
        //        }
        //        x++;
        //    }

        //    return DoCalculate(cfc, charge);
        //}

        //private decimal DoCalculate(CalculateFieldsCollection cfCollection, ChargeEntities charge)
        //{
        //    decimal total = 0;
        //    Type t = typeof(ChargeEntities);

        //    for (int i = 0; i < cfCollection.Count - 1; i++)
        //    {
        //        decimal firtsValue = (decimal)t.GetProperty(cfCollection[i].Field).GetValue(charge, null);
        //        decimal secondValue = (decimal)t.GetProperty(cfCollection[i + 1].Field).GetValue(charge, null);

        //        if (cfCollection[i].OperationType == "+")
        //            total += firtsValue + secondValue;
        //        else
        //            total += firtsValue - secondValue;
        //    }

        //    return total;
        //}

        //private string LoadFileSection(ItemStructureFile item, ChargeEntitiesCollection chargeCollection, int type, string split)
        //{
        //    StringBuilder str = new StringBuilder();

        //    if (item.ValueType.Split('_')[1] == "Fixed")
        //    {
        //        str.Append(StuffValue(item.Value, item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //    }
        //    else
        //    {
        //        Type t = typeof(ChargeEntitiesCollection);
        //        object value = t.GetProperty(item.ValueType).GetValue(chargeCollection, null);
        //        TypeCode typeCode = Type.GetTypeCode(t.GetProperty(item.ValueType).PropertyType);

        //        if (typeCode == TypeCode.Decimal)
        //        {
        //            var amount = (decimal)value;
        //            if (split == "\t")
        //            {
        //                str.Append(StuffValue(amount.ToString("N2").Replace(".", "*").Replace(",", ".").Replace("*", ","), item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //            }
        //            else
        //            {
        //                str.Append(StuffValue(amount.ToString("N2").Replace(".", "").Replace(",", ""), item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //            }

        //        }
        //        else if (typeCode == TypeCode.DateTime)
        //        {
        //            var currentDate = (DateTime)value;
        //            str.Append(StuffValue(currentDate.ToString(item.Format), item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //        }
        //        else
        //        {
        //            str.Append(StuffValue(value.ToString(), item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //        }
        //    }
        //    switch (type)
        //    {
        //        case 1: str.Append(split);
        //            break;
        //    }
        //    return str.ToString();
        //}

        //private string LoadFileSection(ItemStructureFile item, ChargeEntities charge, ChargeEntitiesCollection chargeList, Product product, int type, string split)
        //{
        //    StringBuilder str = new StringBuilder();

        //    if (item.ValueType.Split('_')[1] == "Fixed")
        //    {
        //        str.Append(StuffValue(item.Value, item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //    }
        //    else if (item.ValueType.Split('_')[1] == "Calculate")
        //    {
        //        str.Append(StuffValue(CalculateFields(item, charge).ToString("N2").Replace(".", "").Replace(",", ""), item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //    }
        //    else
        //    {

        //        //Type t = typeof(ChargeEntities);
        //        Type t = GetsType(item.ValueType.Split('_')[0]);
        //        //object value = t.GetProperty(item.ValueType).GetValue(charge, null);
        //        object value = GetValue(item.ValueType.Split('_')[0], t, item, charge, chargeList, product);
        //        if (value==null)
        //        {
        //            Exception ex= new Exception(item.FieldName+" IS EMPTY. ");
        //            ex.HelpLink = "VALUE NULL";
        //            throw ex;
        //        }
                
        //        TypeCode typeCode = Type.GetTypeCode(t.GetProperty(item.ValueType.Split('_')[1]).PropertyType);

        //        if (typeCode == TypeCode.Decimal)
        //        {
        //            var amount = (decimal)value;
        //            if (split=="\t")
        //            {
        //                str.Append(StuffValue(amount.ToString("N2").Replace(".", "*").Replace(",", ".").Replace("*", ","), item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //            }
        //            else
        //            {
        //                str.Append(StuffValue(amount.ToString("N2").Replace(".", "").Replace(",", ""), item.StuffedCharacter, item.Characters, item.StuffedSpot));                        
        //            }
                    
                    
        //        }
        //        else if (typeCode == TypeCode.DateTime)
        //        {
        //            var currentDate = (DateTime)value;
        //            str.Append(StuffValue(currentDate.ToString(item.Format), item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //        }
        //        else
        //        {
        //            str.Append(StuffValue(value.ToString().Trim(), item.StuffedCharacter, item.Characters, item.StuffedSpot));
        //        }
               
               
        //    }
        //    switch (type)
        //    {
        //        case 1: str.Append(split);
        //            break;
        //    }
        //    return str.ToString();
        //}
        
   

        //private string StuffValue(string value, string stuff, int lenght, string spot)
        //{
        //    string finalString = value;

        //    if (value.Length <= lenght)
        //    {
        //        if (spot == "Left")
        //            finalString = finalString.PadLeft(lenght, char.Parse(stuff));
        //        else
        //            finalString = finalString.PadRight(lenght, char.Parse(stuff));
        //    }
        //    else if (value.Length > lenght)
        //    {
        //        finalString = finalString.Substring(0, lenght);
        //    }

        //    return finalString;
        //}

        //private Type GetsType(string type)
        //{
        //    Type t = null;

        //    switch (type)
        //    {
        //        case "ChargeEntities": t = typeof(ChargeEntities);
        //            break;
        //        case "ChargeEntitiesCollection": t = typeof(ChargeEntitiesCollection);
        //            break;
        //        case "Product": t = typeof(Product);
        //            break;
        //    }

        //    return t;
        //}

        //private object GetValue(string type, Type t, ItemStructureFile structure, ChargeEntities charge, ChargeEntitiesCollection chargeList, Product product)
        //{
        //    object value = null;

        //    switch (type)
        //    {
        //        case "ChargeEntities": value = t.GetProperty(structure.ValueType.Split('_')[1]).GetValue(charge, null);
        //            break;
        //        case "ChargeEntitiesCollection": value = t.GetProperty(structure.ValueType.Split('_')[1]).GetValue(chargeList, null);
        //            break;
        //        case "Product": value = t.GetProperty(structure.ValueType.Split('_')[1]).GetValue(product, null);
        //            break;
        //    }

        //    return value;
        //}


        //// Search predicate returns true if accounttypeid match with product code principal or product code aditional".
        //private Predicate<Product> PredicateParameters(string value)
        //{
        //    return new Predicate<Product>(delegate(Product  target)
        //    {
        //        if (target.ProductCode.Trim()  == value.Trim() )
        //            return true;
        //        else
        //            return false;

        //    });
        //}  

        //public void GenerateXls(string str)
        //{
        //        //2. Variables a utilizar (esto es de cada quién, de acuerdo a lo que se vaya a realizar):

        //        string  filePath;

        //        //3. Este paso es sólo para los que tienen EXCEL en inglés y el Sistema Operativo en otro idioma, en mi caso tengo EXCEL 2003 en inglés y Windows XP Profesional en Español. Se deben colocar estas líneas para solucionar un "bug" que trae el Office:

        //        System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
        //        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

        //        //4. Se crea la instancia de la aplicación:

        //        Excel.Application oXL = new Excel.Application();

        //        //5. Lo siguiente es abrir el cuadro de diálogo para salvar el archivo, se hace con la siguiente línea, el primer parámetro es el nombre sugerido que le queramos dar, el segundo es el filtro de extensiones, el tercero es el índice del filtro (cual estará marcado), el cuarto es el título de la ventana y el quinto es el nombre del botón (sólo funciona con Macintosh). El Missing.Value es usado para llenar el campo de los parámetros opcionales y que no van a ser utilzados, se debe hacer así porque en C# no se pueden omitir.

        //        filePath = oXL.GetSaveAsFilename("PRUEBA", "Archivos de Excel (*.xls), *.xls", 1, "Guardar prueba", Missing.Value).ToString();

        //        //6. Se crea el libro y la hoja, se ejecuta el código que necesitemos implementar en el archivo (si es que necesitamos hacerle algo):

        //        Excel._Workbook oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value) ); // crear el Workbook
        //        Excel._Worksheet oSheet = (Excel._Worksheet)oWB.ActiveSheet; // crear la Hoja
        //        oSheet.Cells[1, 1] = "HOLA"; //Columna,Fila
        //        oSheet.Cells[1, 2] = "MUNDO";

        //        //7. Algo de formato:
        //        int A1 = 1;
        //        int A2 = 2;
        //        oSheet.Name = "Ingreso de Equipo"; //Nombre de la hora
        //        oSheet.get_Range(A1, A2).Font.Bold = true; //Letra negrita
        //        oSheet.get_Range(A1, A2).Interior.ColorIndex = 9; //Color de Fondo, 9 es rojo oscuro, entre 0-56
        //        oSheet.get_Range(A1, A2).Font.ColorIndex = 2; //Color de letra, 2 es blanco, entre 0-56
        //        oSheet.get_Range(A1, A2).WrapText = true; //Respetar ancho de la celda
        //        oSheet.get_Range(A1, A2).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//Alineación horizontal
        //        oSheet.get_Range(A1, A2).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //Alineación vertical
        //        oSheet.get_Range(A1, A2).ColumnWidth = 20; //Ancho de la columna
        //        oSheet.get_Range(A1, A2).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); //Borde

        //        //Hay muchas opciones, sólo es de probar hasta conseguir lo que necesitemos.

        //        //8. Si se desea visualizar el archivo se agregan estas líneas, si no, se puede omitir sin problemas.

        //        oXL.Visible = true;
        //        oXL.UserControl = true;

        //        //9. Guardamos el archivo y lo cerramos

        //        oXL.ActiveWorkbook.Close(true, filePath, Type.Missing);
        //        oXL.Quit();
        //        oXL = null;

        //        //10. Limpiamos cualquier proceso de Excel que haya quedado abierto luego de crear el archivo y regeneramos el archivo Culture (el del bug, para dejarlo como al inicio).

        //        System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI; // restaura el CultureInfo
        //        System.Diagnostics.Process[] myProcesses;
        //        myProcesses = System.Diagnostics.Process.GetProcessesByName("EXCEL");
        //        foreach (System.Diagnostics.Process instance in myProcesses) {
        //        instance.CloseMainWindow();
        //        instance.Kill();
        //        instance.Close();
        //        }

                

        //}
        //public static string ConvertExtractOutput(int ExtractOutput)
        //{
        //    switch (ExtractOutput)
        //    {
        //        case (int)GeneralEnumTypes.ExtractOutput.Charge: return "Collection Extract";
        //        case (int)GeneralEnumTypes.ExtractOutput.Affilation: return "Affilation Extract";
        //        case (int)GeneralEnumTypes.ExtractOutput.Disaffilation: return "Disaffilation Extract";
        //        case (int)GeneralEnumTypes.ExtractOutput.UpdateGmac: return "Upload Gmac";
        //        case (int)GeneralEnumTypes.ExtractOutput.UpdateServicingFederal: return "Upload Servicing Federal";
        //        default: return "";
        //    }
        //}
        //public static Hashtable FillExtractoutput()
        //{
        //    Hashtable ht = new Hashtable();
        //    foreach (int i  in Enum.GetValues(typeof(GeneralEnumTypes.ExtractOutput)))
        //    {
        //        ht.Add(i, ConvertExtractOutput(i));
        //    }
        //    return ht;
        //}
        //public static string ConvertStructureType(int StructureType)
        //{
        //    switch (StructureType)
        //    {
        //        case (int)GeneralEnumTypes.StructureType.Update: return "Update";
        //        case (int)GeneralEnumTypes.StructureType.Upload_Not_Domiciliated_File: return "Upload Not Domiciliated File";
        //        default: return "";
        //    }
        //}
        //public static Hashtable FillStructureType()
        //{
        //    Hashtable ht = new Hashtable();
        //    foreach (int i  in Enum.GetValues(typeof(GeneralEnumTypes.StructureType)))
        //    {
        //        ht.Add(i, ConvertStructureType(i));
        //    }
        //    return ht;
        //}
        //public static string ConvertType_SpecialPayment(int Type_SpecialPayment)
        //{
        //    switch (Type_SpecialPayment)
        //    {
        //        case (int)GeneralEnumTypes.Type_SpecialPayment.Amortization: return "Principal Reduction";
        //        case (int)GeneralEnumTypes.Type_SpecialPayment.Installment: return "Installment";
        //        case (int)GeneralEnumTypes.Type_SpecialPayment.Late_Charge: return "Late Charges";
        //        case (int)GeneralEnumTypes.Type_SpecialPayment.Total_Payment: return "Early Payoff";
        //        case (int)GeneralEnumTypes.Type_SpecialPayment.Flat_Amount : return "Flat Amount";
        //        default: return "";
        //    }
        //}
        //public static string ConvertType_SpecialPayment(string Type_SpecialPayment)
        //{
        //    switch (Type_SpecialPayment)
        //    {
        //        case "Amortization": return "Principal Reduction";
        //        case "Installment": return "Installment";
        //        case "Late_Charge": return "Late Charges";
        //        case "Total_Payment": return "Early Payoff";
        //        case "Flat_Amount": return "Flat Amount";
        //        default: return "";
        //    }
        //}
        //public static Hashtable FillType_SpecialPayment()
        //{
        //    Hashtable ht = new Hashtable();
        //    foreach (int i in Enum.GetValues(typeof(GeneralEnumTypes.Type_SpecialPayment)))
        //    {
        //        ht.Add(i, ConvertType_SpecialPayment(i));
        //    }
        //    return ht;
        //}
        //public static string ConvertPeriodCharge(int PeriodCharge)
        //{
        //    switch (PeriodCharge)
        //    {
        //        case (int)GeneralEnumTypes.PeriodCharge.Daily: return "Dayly File";
        //        case (int)GeneralEnumTypes.PeriodCharge.AnotherDaily: return "Special Dayly File";
        //        case (int)GeneralEnumTypes.PeriodCharge.Late_Charge: return "Late Charges File";
        //        case (int)GeneralEnumTypes.PeriodCharge.SpecialPay: return "Special Payments";
        //        case (int)GeneralEnumTypes.PeriodCharge.Mass: return "Past Due File";
        //        case (int)GeneralEnumTypes.PeriodCharge.AnotherMass: return "Special Past Due File";
        //        case (int)GeneralEnumTypes.PeriodCharge.Accts_Not_Affiliated: return "Accts Not Affiliated";
        //        default: return "";
        //    }
        //}
        //public static ArrayList  FillPeriodCharge()
        //{
        //    ArrayList ar = new ArrayList(); 
        //   Element ele = new Element(); 
        //    foreach (int i in Enum.GetValues(typeof(GeneralEnumTypes.PeriodCharge)))
        //    {
        //        ele = new Element();
        //        ele.Id = i;
        //        ele.Name = ConvertPeriodCharge(i);
        //        ar.Add (ele);
        //    }
        //    ar.Sort();
        //    return ar;
        //}
        //private class Element : IComparable 
        //{
        //    public int Id { get; set; }

        //    public String Name { get; set; }

        //    public int CompareTo(object obj)
        //    {
        //        Element comp = (Element)obj;
        //        int result = this.Name.CompareTo(comp.Name);
        //        if (result == 0)
        //            result = this.Name.CompareTo(comp.Name);
        //        return result; 
        //    }
        //}

        //#region para generar archivos excel de las diferentes colecciones
        //public StringBuilder BuildChargeEntitiesDataTable(ChargeEntitiesDataTableCollection Col, string split) 
        //{
        //        #region Construir excel para ResponseFile
        //            StringBuilder str = new StringBuilder();
        //            if (split=="\t")
        //            {
        //                str.Append("Account Number" + split);
        //                str.Append("Schedule Payment" + split);
        //                str.Append("Response Description" + split);
        //                str.Append("Payment Sequence Number" + split);
        //                str.Append("Response BankID" + split);
        //                str.Append("Debit AccounNumber" + split);
        //                str.Append("Due Date" + split);
        //                str.Append("Product Type" + split);
        //                str.AppendLine();
        //                if (Col!=null)
        //                {
        //                    foreach (ChargeEntitiesDataTable charge in Col)
        //                    {
        //                        str.Append(charge.AccountNumber + split);
        //                        str.Append(charge.SchedulePayment + split);
        //                        str.Append(charge.Response + split);
        //                        str.Append(charge.PaymentSequenceNumber + split);
        //                        str.Append(charge.ResponseBankID + split);
        //                        str.Append(charge.DebitAccounNumber + split);
        //                        str.Append(charge.DueDate + split);
        //                        str.Append(charge.ProductType + split);
        //                        str.AppendLine();
        //                    } 
        //                }
                        
        //            }
        //            else
        //            {
        //                    str.Append(StuffValue("Account Number", " ", 24, "Right"));
        //                    str.Append(StuffValue("Schedule Payment", " ", 24, "Right"));
        //                    str.Append(StuffValue("Response Description", " ", 34, "Right"));
        //                    str.Append(StuffValue("Payment Sequence Number", " ", 24, "Right"));
        //                    str.Append(StuffValue("Response BankID", " ", 24, "Right") + split);
        //                    str.Append(StuffValue("Debit AccounNumber", " ", 24, "Right"));
        //                    str.Append(StuffValue("Due Date", " ", 24, "Right"));
        //                    str.Append(StuffValue("Product Type", " ", 24, "Right"));
        //                    str.AppendLine();
        //                    if (Col!=null)
        //                    {
        //                        foreach (ChargeEntitiesDataTable charge in Col)
        //                        {
        //                            str.Append(StuffValue(charge.AccountNumber, " ", 24, "Right"));
        //                            str.Append(StuffValue(charge.SchedulePayment, " ", 24, "Right"));
        //                            str.Append(StuffValue(charge.Response, " ", 34, "Right"));
        //                            str.Append(StuffValue(charge.PaymentSequenceNumber, " ", 24, "Right"));
        //                            str.Append(StuffValue(charge.ResponseBankID, " ", 24, "Right"));
        //                            str.Append(StuffValue(charge.DebitAccounNumber, " ", 24, "Right"));
        //                            str.Append(StuffValue(charge.DueDate, " ", 24, "Right"));
        //                            str.Append(StuffValue(charge.ProductType, " ", 24, "Right"));
        //                            str.AppendLine();
        //                        }
        //                    }
                       
        //            }
                    
        //            return str;
        //        #endregion
        //}
        //public StringBuilder BuildTemporaryChargesDataTable(TemporaryChargesCollection Col, string split)
        //{
        //         #region Construir excel para Reverse Charges
        //    StringBuilder str = new StringBuilder();
        //    if (split == "\t")
        //    {
        //        str.Append("Account Number" + split);
        //        str.Append("Payment Sequence Number" + split);
        //        str.Append("Due Date" + split);
        //        str.Append("Schedule Payment" + split);
        //        str.Append("Rejection Reason" + split);
        //        str.AppendLine();
        //        if (Col!=null)
        //        {
        //            foreach (TemporaryCharges charge in Col)
        //            {
        //                str.Append(charge.AccountNumber + split);
        //                str.Append(charge.PaymenSequenceNumber + split);
                        

        //                if (charge.DueDate.ToString() == "")
        //                {
        //                    str.Append(charge.DueDate.ToString()+ split);
        //                }
        //                else
        //                {
        //                    string s = charge.DueDate.ToString();
        //                    DateTime d = DateTime.Parse(s);
        //                    str.Append(d.ToString("M/d/yyyy")+split);
        //                }
        //                //str.Append(charge.Amount + split);
        //                if (charge.Amount.ToString() == "")
        //                {
        //                    str.Append(charge.Amount.ToString()+split);
        //                }
        //                else
        //                {
        //                    string s = charge.Amount.ToString();
        //                    decimal d = decimal.Parse(s);
        //                    s = d.ToString("N2");
        //                    str.Append(s.ToString()+split);
        //                }
        //                str.Append(charge.RejectionReason + split);
        //                str.AppendLine();
        //            }
        //        }
                
        //    }
        //    else
        //    {
        //        str.Append(StuffValue("Account Number", " ", 24, "Right"));
        //        str.Append(StuffValue("Payment Sequence Number", " ", 24, "Right"));
        //        str.Append(StuffValue("Due Date", " ", 34, "Right"));
        //        str.Append(StuffValue("Schedule Payment", " ", 24, "Right"));
        //        str.Append(StuffValue("Rejection Reason", " ", 24, "Right"));
        //        str.AppendLine();
        //        if (Col!=null)
        //        {
        //            foreach (TemporaryCharges charge in Col)
        //            {
        //                str.Append(StuffValue(charge.AccountNumber, " ", 24, "Right"));
        //                str.Append(StuffValue(charge.PaymenSequenceNumber.ToString(), " ", 24, "Right"));
        //                if (charge.DueDate.ToString() == "")
        //                { 
        //                    str.Append(StuffValue(charge.DueDate.ToString(), " ", 34, "Right"));
        //                }
        //                else
        //                {
        //                    string s = charge.DueDate.ToString();
        //                    DateTime d = DateTime.Parse(s);
        //                    str.Append(StuffValue(d.ToString("M/d/yyyy"), " ", 34, "Right"));
        //                }
        //                if (charge.Amount.ToString()=="")
        //                {
        //                    str.Append(StuffValue(charge.Amount.ToString(), " ", 24, "Right"));
        //                }
        //                else
        //                {
        //                    string s = charge.Amount.ToString();
        //                    decimal d = decimal.Parse(s);
        //                    s = d.ToString("N2");
        //                    str.Append(StuffValue(s.ToString(), " ", 24, "Right"));
        //                }
                       
        //                str.Append(StuffValue(charge.RejectionReason, " ", 24, "Right"));
        //                str.AppendLine();
        //            } 
        //        }
                
        //    }

        //    return str;
        //    #endregion
        //}
        
        //# endregion
        //# endregion


        #region "Mis Metodos"

            /// <summary>
            /// Dado una lista de valores genera un archivo en excel
            /// </summary>
            /// <param name="strBuilder"></param>
            /// <param name="Response"></param>
            public void GenerateFilexls(StringBuilder strBuilder, HttpResponse Response)
            {
                Response.Clear();
                Response.AddHeader("content-disposition",
                                   String.Format("attachment; filename={0}.xls", fileName));
                Response.Charset = "";
                Response.ContentType = "application/x-excel";
                Response.Write(strBuilder.ToString());
                Response.End();
            } // GenerateFilexls

            /// <summary>
            /// Dado los parametros retorna un nombre de archivo unico con la fecha y la hora
            /// </summary>
            /// <param name="nameFile"></param>
            /// <param name="operation"></param>
            /// <returns></returns>
            public string BuildFileName(string nameFile, string operation)
            {
                return nameFile + operation + BuildDateName();
            } // BuildFileName

            /// <summary>
            /// Obtiene la fecha y hora del sistema para concatenarlos a los archivos
            /// </summary>
            /// <returns></returns>
            public string BuildDateName()
            {
                string Date = DateTime.Now.ToString("ddMMyyyy");
                string Hora = DateTime.Now.ToString("yyyyMMddHHmmss");
                Hora = Hora.Substring(8, 6);
                return Date + "_" + Hora;
            } // BuildDateName

            /// <summary>
            /// Genera un string con los valores a exportar al excel
            /// </summary>
            /// <param name="Col"></param>
            /// <param name="split"></param>
            /// <returns></returns>
            public StringBuilder BuildDataTable_AdditionalInformationDealer(StringAdditionalInformationDealerCollection Col, string split)
            {
                StringBuilder str = new StringBuilder();
                str.Append("Dealer Number" + split);
                str.Append("Month" + split);
                str.Append("GMAC Academy" + split);
                str.Append("Twitter" + split);
                str.Append("Rejection Reason" + split);
                str.AppendLine();

                if (Col != null)
                {
                    foreach (StringAdditionalInformationDealer additional in Col)
                    {
                        str.Append(additional.AdditionalInformationDealerNumber + split);
                        str.Append(additional.AdditionalInformationDealerMonth + split);
                        str.Append(additional.AdditionalInformationDealerGMACAcademy + split);
                        str.Append(additional.AdditionalInformationDealerTwiter + split);
                        str.Append(additional.RejectionReason + split);

                        str.AppendLine();
                    } // foreach
                } // if (Col != null)

                return str;
            } // BuildDataTable_AdditionalInformationDealer

            /// <summary>
            /// Genera un string con los valores a exportar al excel
            /// </summary>
            /// <param name="Col"></param>
            /// <param name="split"></param>
            /// <returns></returns>
            public StringBuilder BuildDataTable_ASLTelecom(StringASLTelecomCollection Col, string split)
            {
                StringBuilder str = new StringBuilder();
                str.Append("Date" + split);
                str.Append("Message Type" + split);
                str.Append("Records Received" + split);
                str.Append("Records Sent" + split);
                str.Append("Records Not Loaded" + split);
                str.Append("Rejection Reason" + split);
                str.AppendLine();

                if (Col != null)
                {
                    foreach (StringASLTelecom additional in Col)
                    {
                        str.Append(additional.dDate + split);
                        str.Append(additional.MessageType + split);
                        str.Append(additional.RecordsReceived + split);
                        str.Append(additional.RecordsSent + split);
                        str.Append(additional.RecordsNotLoaded + split);
                        str.Append(additional.RejectionReason + split);

                        str.AppendLine();
                    } // foreach
                } // if (Col != null)

                return str;
            } // BuildDataTable_ASLTelecom

            /// <summary>
            /// Genera un string con los valores a exportar al excel
            /// </summary>
            /// <param name="Col"></param>
            /// <param name="split"></param>
            /// <returns></returns>
            public StringBuilder BuildDataTable_LaoSalvageAccount(StringLaoSalvageAccountCollection Col, string split)
            {
                StringBuilder str = new StringBuilder();
                str.Append("Account Number" + split);
                str.Append("Account Status Code" + split);
                str.Append("Account Status Date" + split);
                str.Append("Dealer Name" + split);
                str.Append("Dealer Number" + split);
                str.Append("Third Party Name" + split);
                str.Append("Third Party Type Code" + split);
                str.Append("Current Total Amount" + split);
                str.Append("Rejection Reason" + split);
                str.AppendLine();

                if (Col != null)
                {
                    foreach (StringLaoSalvageAccount additional in Col)
                    {
                        str.Append(additional.AccountNumber + split);
                        str.Append(additional.AccountStatusCode + split);
                        str.Append(additional.AccountStatusDate + split);
                        str.Append(additional.DealerName + split);
                        str.Append(additional.DealerNumber + split);
                        str.Append(additional.ThirdPartyName + split);
                        str.Append(additional.ThirdPartyTypeCode + split);
                        str.Append(additional.CurrentTotalAmount + split);
                        str.Append(additional.RejectionReason + split);

                        str.AppendLine();
                    } // foreach
                } // if (Col != null)

                return str;
            } // BuildDataTable_LaoSalvageAccount

            /// <summary>
            /// Genera un string con los valores a exportar al excel
            /// </summary>
            /// <param name="Col"></param>
            /// <param name="split"></param>
            /// <returns></returns>
            public StringBuilder BuildDataTable_PlantFile(StringPlantFileCollection Col, string split)
            {
                StringBuilder str = new StringBuilder();
                str.Append("RetailInvoiceDate" + split);
                str.Append("Dealer" + split);
                str.Append("DealerNumber" + split);
                str.Append("VehicleIdentification" + split);
                str.Append("ModelCode" + split);
                str.Append("ModelDescription" + split);
                str.Append("WholesaleDistributionChannel" + split);
                str.Append("Estatus" + split);
                str.Append("PaymentForm" + split);
                str.Append("PlaquePatent" + split);
                str.Append("ContactName" + split);
                str.Append("ContactLastName" + split);
                str.Append("Year" + split);
                str.Append("ContactAddress" + split);
                str.Append("ContactMobilePhone" + split);
                str.Append("residencyTelephoneContact" + split);
                str.Append("ContactCity" + split);
                str.Append("DeliveryDate" + split);
                str.Append("ZoneID" + split);
                str.Append("SalesDetail" + split);
                str.Append("SectorSales" + split);
                str.Append("PlanFileType" + split);
                str.Append("Rejection Reason" + split);
                str.AppendLine();

                if (Col != null)
                {
                    foreach (StringPlantFile additional in Col)
                    {
                        str.Append(additional.RetailInvoiceDate + split);
                        str.Append(additional.Dealer + split);
                        str.Append(additional.DealerNumber + split);
                        str.Append(additional.VehicleIdentification + split);
                        str.Append(additional.ModelCode + split);
                        str.Append(additional.ModelDescription + split);
                        str.Append(additional.WholesaleDistributionChannel + split);
                        str.Append(additional.Estatus + split);
                        str.Append(additional.PaymentForm + split);
                        str.Append(additional.PlaquePatent + split);
                        str.Append(additional.ContactName + split);
                        str.Append(additional.ContactLastName + split);
                        str.Append(additional.Year + split);
                        str.Append(additional.ContactAddress + split);
                        str.Append(additional.ContactMobilePhone + split);
                        str.Append(additional.residencyTelephoneContact + split);
                        str.Append(additional.ContactCity + split);
                        str.Append(additional.DeliveryDate + split);
                        str.Append(additional.ZoneID + split);
                        str.Append(additional.SalesDetail + split);
                        str.Append(additional.SectorSales + split);
                        str.Append(additional.PlanFileType + split);
                        str.Append(additional.RejectionReason + split);

                        str.AppendLine();
                    } // foreach
                } // if (Col != null)

                return str;
            } // BuildDataTable_PlantFile

        #endregion

    } // class
} // namespace
